package com.millstein.tsinglog.mapper.biz;

import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;
import org.springframework.stereotype.Repository;

import java.util.List;
import java.util.Map;

@Repository
public interface StatisticsMapper {

    @Select("select count(1) from biz_article where status = 1 and create_time > #{date}")
    Integer getArticleCountByDate(@Param("date") String dateStr);

    @Select("select count(1) from biz_category where status = 1 and create_time > #{date}")
    Integer getCategoryCountByDate(@Param("date") String dateStr);

    @Select("select count(1) from biz_tag where status = 1 and create_time > #{date}")
    Integer getTagCountByDate(@Param("date") String dateStr);

    @Select("select count(1) from biz_comment where status = 1 and create_time > #{date}")
    Integer getCommentCountByDate(@Param("date") String dateStr);

    @Select("select c.category_name as name, count(*) as y\n" +
            "from biz_article a\n" +
            "left join biz_category c on a.category_id = c.id\n" +
            "where a.status = 1 and c.status = 1\n" +
            "group by category_id;")
    List<Map<String, Object>> showArticleCountSortedByCategory();

    @Select("select t.tag_name as name, count(*) as y\n" +
            "from biz_article a\n" +
            "left join biz_article_tag at on a.id = at.aid\n" +
            "left join biz_tag t on at.tid = t.id\n" +
            "where a.status = 1 and t.status = 1\n" +
            "group by t.id")
    List<Map<String, Object>> showArticleCountSortedByTag();
}
